﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common.Dao.EsDao;
using Common.Model;
using Common.util;
using Dapper;
using MoreLinq;
using MySql.Data.MySqlClient;

namespace Common.Dao
{
    public class QuestionAnswerDao
    {
        //保存问题与回答
        public static void BatchSaveQuestion(List<QAnswer> list)
        {
            QuestionAnswerEsDao.SaveQuestion(list);

            int batchSize = 100;
            var batchData = list.Batch(batchSize);
            foreach (IEnumerable<QAnswer> data in batchData)
            {
                var sql =
                    new StringBuilder("INSERT IGNORE question_answer(pid,code,question_time,question,`from`) VALUES ");
                bool first = true;
                foreach (QAnswer question in data)
                {
                    if (first)
                    {
                        first = false;
                    }
                    else
                    {
                        sql.Append(",");
                    }
                    sql.Append(
                        $"('{question.Pid}'" +
                        $",'{question.Code}'" +
                        $",'{question.QuestionTime}'" +
                        $",'{MySqlHelper.DoubleQuoteString(question.Question)}'" +
                        $",'{question.From}')");
                }

                using (var cnn = DbUtils.GetHotTopicConnection())
                {
                    cnn.Execute(sql.ToString());
                }
            }
        }

        //保存问题与回答
        public static void BatchSaveAnswer(List<QAnswer> list)
        {
            QuestionAnswerEsDao.SaveAnswer(list);

            int batchSize = 100;
            var batchData = list.Batch(batchSize);
            foreach (IEnumerable<QAnswer> data in batchData)
            {
                var sql =
                    new StringBuilder(
                        "INSERT INTO question_answer(pid,code,question_time,answer_time,question,answer,`from`) VALUES ");
                bool first = true;
                foreach (QAnswer question in data)
                {
                    if (first)
                    {
                        first = false;
                    }
                    else
                    {
                        sql.Append(",");
                    }
                    sql.Append(
                        $"('{MySqlHelper.DoubleQuoteString(question.Pid)}'" +
                        $",'{question.Code}'" +
                        $",'{question.QuestionTime}'" +
                        $",'{question.AnswerTime}'" +
                        $",'{MySqlHelper.DoubleQuoteString(question.Question)}'" +
                        $",'{MySqlHelper.DoubleQuoteString(question.Answer)}'" +
                        $",'{question.From}')");
                }
                sql.Append(" ON DUPLICATE KEY UPDATE answer_time = VALUES(answer_time),answer = VALUES(answer)");
                using (var cnn = DbUtils.GetHotTopicConnection())
                {
                    cnn.Execute(sql.ToString());
                }
            }
        }


        //获取问题
        public static List<QAnswer> GetQuestion()
        {
            String sql =
                @"SELECT pid,code,question_time AS QuestionTime,answer_time AS AnswerTime,question,answer FROM `question_answer` ";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<QAnswer>(sql)?.ToList();
            }
        }

        public static List<QAnswer> GetAll()
        {
            String sql =
                @"SELECT pid,code,question_time AS QuestionTime,answer_time AS AnswerTime,question,answer,update_time AS UpdateTime,`from` AS `From` FROM `question_answer` ";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<QAnswer>(sql)?.ToList();
            }
        }

        //获取最新问题
        public static QAnswer GetLastestQuestion()
        {
            String sql =
                @"SELECT pid,code,question_time AS QuestionTime,answer_time AS AnswerTime,question,answer FROM `question_answer` ORDER BY question_time DESC LIMIT 1";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                var qAnswers = conn.Query<QAnswer>(sql)?.ToList();
                if (null != qAnswers && qAnswers.Any())
                {
                    return qAnswers.First();
                }

                return null;
            }
        }

        //获取最新问题
        public static QAnswer GetLastestShQuestion()
        {
            String sql =
                @"SELECT pid,code,question_time AS QuestionTime,answer_time AS AnswerTime,question,answer FROM `question_answer` where `from` ='SH' and (answer is null or answer = '') ORDER BY question_time DESC LIMIT 1";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                var qAnswers = conn.Query<QAnswer>(sql)?.ToList();
                if (null != qAnswers && qAnswers.Any())
                {
                    return qAnswers.First();
                }

                return null;
            }
        }

        //获取最新问题
        public static QAnswer GetLastestSzQuestion()
        {
            String sql =
                @"SELECT pid,code,question_time AS QuestionTime,answer_time AS AnswerTime,question,answer FROM `question_answer` where `from` ='SZ' and (answer is null or answer = '') ORDER BY question_time DESC LIMIT 1";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                var qAnswers = conn.Query<QAnswer>(sql)?.ToList();
                if (null != qAnswers && qAnswers.Any())
                {
                    return qAnswers.First();
                }

                return null;
            }
        }

        //获取最新问题
        public static QAnswer GetLastestShAnswer()
        {
            String sql =
                @"SELECT pid,code,question_time AS QuestionTime,answer_time AS AnswerTime,question,answer FROM `question_answer` where `from` ='SH' and (answer is not null or answer != '') ORDER BY question_time DESC LIMIT 1";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                var qAnswers = conn.Query<QAnswer>(sql)?.ToList();
                if (null != qAnswers && qAnswers.Any())
                {
                    return qAnswers.First();
                }

                return null;
            }
        }

        //获取最新问题
        public static QAnswer GetLastestSzAnswer()
        {
            String sql =
                @"SELECT pid,code,question_time AS QuestionTime,answer_time AS AnswerTime,question,answer FROM `question_answer` where `from` ='SZ' and (answer is not null or answer != '') ORDER BY question_time DESC LIMIT 1";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                var qAnswers = conn.Query<QAnswer>(sql)?.ToList();
                if (null != qAnswers && qAnswers.Any())
                {
                    return qAnswers.First();
                }

                return null;
            }
        }


        //获取最新问题
        public static QAnswer GetLastestAnswer()
        {
            String sql =
                @"SELECT pid,code,question_time AS QuestionTime,answer_time AS AnswerTime,question,answer FROM `question_answer` WHERE answer is not null or answer != '' ORDER BY answer_time DESC LIMIT 1";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                var qAnswers = conn.Query<QAnswer>(sql)?.ToList();
                if (null != qAnswers && qAnswers.Any())
                {
                    return qAnswers.First();
                }

                return null;
            }
        }
    }
}